using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
namespace LdExpress
{
    public class CostAccounting
    {
        public long ID = -1;
        public long CostingID = 0;
        public string FileNumber = "";
        public string POL = "";
        public string POD = "";
        public DateTime ETD = DateTime.Now;
        public DateTime ETA = DateTime.Now;
        public string Agent = "";
        public string CarrSale = "";
        public string Customer = "";
        public string CustPic = "";
        public string MBLNo = "";
        public string HBLNo = "";
        public string Freight = "";
        public long Liabilities = 0;
        public double Interest = 0;
        public double TB_Customer = 0;
        public double TB_Agent = 0;
        public string SaleName = "";
        public string SaleOfProvince = "";
        public string AccountingName = "";
        public DateTime AccountingDate = DateTime.Now;
        public string InvoicesNo = "";
        public DateTime InvoicesDate = DateTime.Now;
        public string InvoicesNoBuy = "";
        public DateTime InvoicesDateBuy = DateTime.MinValue;
        public double RateBuy = 0;
        public double RateSell = 0;
        public long IdProvince = 0;
        public long SaleID = 0;
        public long AccountingID = 0;
        public string Location = "";
        public int CloseFile = 0;
        public string InvoicesBuyer = "";
        public string Note = "";
        public CostAccounting()
        {
        }

        public CostAccounting(long lID)
        {
            this.ID = lID;
        }

        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_cost_accounting";

            if (ID <= 0) // new object, we insert new record to database
            {
                strSQL = "INSERT INTO " + strTableName +
                    "(CostingID, " +
                    "FileNumber, " +
                    "POL, " +
                    "POD, " +
                    "ETD, " +
                    "ETA, " +
                    "Agent, " +
                    "CarrSale, " +
                    "Customer," +
                    "CustPic, " +
                    "MBLNo, " +
                    "HBLNo, " +
                    "Freight, " +
                    "Liabilities, " +
                    "Interest, " +
                    "TB_Customer," +
                    "TB_Agent, " +
                    "SaleName, " +
                    "SaleOfProvince, " +
                    "AccountingName, " +
                    "AccountingDate, " +
                    "InvoicesNo, " +
                    "InvoicesDate, " +
                    "InvoicesNoBuy, " +
                    "InvoicesDateBuy, " +
                    "RateBuy, " +
                    "RateSell," +
                    "SaleID," +
                    "AccountingID," +
                    "IdProvince," +
                    "Location" +
                    ",CloseFile" +
                    ",InvoicesBuyer" +
                    ",Note" +
                    ")" +
                    "VALUES" +
                    "(" +
                    CostingID.ToString() + "," +
                    "'" + FileNumber.ToString() + "'," +
                    "'" + DBModule.RefineString(POL) + "'," +
                    "'" + DBModule.RefineString(POD) + "'," +
                    DBModule.RefineDatetime(ETD) + "," +
                    DBModule.RefineDatetime(ETA) + "," +
                    "'" + DBModule.RefineString(Agent) + "'," +
                    "'" + DBModule.RefineString(CarrSale) + "'," +
                    "'" + DBModule.RefineString(Customer) + "'," +
                    "'" + DBModule.RefineString(CustPic) + "'," +
                    "'" + DBModule.RefineString(MBLNo) + "'," +
                    "'" + DBModule.RefineString(HBLNo) + "'," +
                    "'" + DBModule.RefineString(Freight) + "'," +
                    Liabilities.ToString() + "," +
                    Interest.ToString() + "," +
                    TB_Customer.ToString() + "," +
                    TB_Agent.ToString() + "," +
                    "'" + DBModule.RefineString(SaleName) + "'," +
                    "'" + DBModule.RefineString(SaleOfProvince) + "'," +
                    "'" + DBModule.RefineString(AccountingName) + "'," +
                    DBModule.RefineDatetime(AccountingDate) + "," +
                    "'" + DBModule.RefineString(InvoicesNo) + "'," +
                    DBModule.RefineDatetime(InvoicesDate) + "," +
                    "'" + DBModule.RefineString(InvoicesNoBuy) + "'," +
                    DBModule.RefineDatetime(InvoicesDateBuy) + "," +
                    RateBuy.ToString() + "," +
                    RateSell.ToString() + "," +
                    SaleID.ToString() + "," +
                    AccountingID.ToString() + "," +
                    IdProvince.ToString() + "," +
                    "'" + DBModule.RefineString(Location) + "'" +
                    "," + CloseFile.ToString() +
                    ",'" + DBModule.RefineString(InvoicesNoBuy) + "'" +
                    ",'" + DBModule.RefineDatetime(Note) + "'" +
                    ")";
                // run SQL statement
                //throw new Exception(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("CostAccounting", "add", cn, trans);
            }
            else // edit object
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                    "CostingID = " + CostingID.ToString() + "," +
                    "FileNumber = '" + DBModule.RefineString(FileNumber.ToString()) + "'," +
                    "POL = '" + DBModule.RefineString(POL) + "'," +
                    "POD = '" + DBModule.RefineString(POD) + "'," +
                    "ETD = " + DBModule.RefineDatetime(ETD) + "," +
                    "ETA = " + DBModule.RefineDatetime(ETA) + "," +
                    "Agent = '" + DBModule.RefineString(Agent) + "'," +
                    "CarrSale = '" + DBModule.RefineString(CarrSale) + "'," +
                    "Customer = '" + DBModule.RefineString(Customer) + "'," +
                    "CustPic = '" + DBModule.RefineString(CustPic) + "'," +
                    "MBLNo = '" + DBModule.RefineString(MBLNo) + "'," +
                    "HBLNo = '" + DBModule.RefineString(HBLNo) + "'," +
                    "Freight = '" + DBModule.RefineString(Freight) + "'," +
                    "Liabilities = " + Liabilities + "," +
                    "Interest = " + Interest.ToString() + "," +
                    "TB_Customer = " + TB_Customer.ToString() + "," +
                    "TB_Agent = " + TB_Agent.ToString() + "," +
                    "SaleName = '" + DBModule.RefineString(SaleName) + "'," +
                    "SaleOfProvince = '" + DBModule.RefineString(SaleOfProvince) + "'," +
                    "AccountingName = '" + DBModule.RefineString(AccountingName) + "'," +
                    "AccountingDate = " + DBModule.RefineDatetime(AccountingDate) + "," +
                    "InvoicesNo = '" + DBModule.RefineString(InvoicesNo) + "'," +
                    "InvoicesDate = " + DBModule.RefineDatetime(InvoicesDate) + "," +
                    "InvoicesNoBuy = '" + DBModule.RefineString(InvoicesNoBuy) + "'," +
                    "InvoicesDateBuy = " + DBModule.RefineDatetime(InvoicesDateBuy) + "," +
                    "RateBuy = " + RateBuy.ToString() + "," +
                    "IdProvince = " + IdProvince.ToString() + "," +
                    "SaleID = " + SaleID.ToString() + "," +
                    "AccountingID = " + AccountingID.ToString() + "," +
                    "RateSell = " + RateSell.ToString() + "," +
                    "Location = '" + DBModule.RefineString(Location) + "'" +
                    ",CloseFile = " + CloseFile.ToString() +
                    ",InvoicesBuyer = '" + DBModule.RefineDatetime(InvoicesBuyer) + "'" +
                    ",Note = '" + DBModule.RefineDatetime(Note) + "'" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                //throw new Exception(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("CostAccounting", "edit", cn, trans);
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "DELETE FROM tbl_cost_accounting WHERE ID = " + this.ID.ToString();
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("CostAccounting", "delete", cn, trans);
        }
        public static void DoCloseFile(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "Update tbl_cost_accounting set CloseFile=1";
            DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
        }
        public static void DoOpenFile(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "Update tbl_cost_accounting set CloseFile=0";
            DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
        }
        public static int GetCountStatusFile(int StatusFile, MySqlConnection cn, MySqlTransaction trans)
        {
            int retVal = 0;
            string sqlCommand = "Select Count(ID) From  tbl_cost_accounting where CloseFile=" + StatusFile;
            string Status = DBModule.ExecuteQueryForOneResult(sqlCommand, cn, trans);
            if (Status != null) retVal = int.Parse(Status);
            return retVal;
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_cost_accounting WHERE ID = " + ID.ToString() + " limit 0,1";
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("CostingID")) CostingID = long.Parse(dr["CostingID"].ToString());
                if (!dr.IsNull("FileNumber")) FileNumber = dr["FileNumber"].ToString();
                if (!dr.IsNull("POL")) POL = dr["POL"].ToString();
                if (!dr.IsNull("POD")) POD = dr["POD"].ToString();
                if (!dr.IsNull("ETD")) ETD = DateTime.Parse(dr["ETD"].ToString());
                if (!dr.IsNull("ETA")) ETA = DateTime.Parse(dr["ETA"].ToString());
                if (!dr.IsNull("Agent")) Agent = dr["Agent"].ToString();
                if (!dr.IsNull("CarrSale")) CarrSale = dr["CarrSale"].ToString();
                if (!dr.IsNull("Customer")) Customer = dr["Customer"].ToString();
                if (!dr.IsNull("CustPic")) CustPic = dr["CustPic"].ToString();
                if (!dr.IsNull("MBLNo")) MBLNo = dr["MBLNo"].ToString();
                if (!dr.IsNull("HBLNo")) HBLNo = dr["HBLNo"].ToString();
                if (!dr.IsNull("Freight")) Freight = dr["Freight"].ToString();
                if (!dr.IsNull("Liabilities")) Liabilities = long.Parse(dr["Liabilities"].ToString());
                if (!dr.IsNull("Interest")) Interest = double.Parse(dr["Interest"].ToString());
                if (!dr.IsNull("TB_Customer")) TB_Customer = double.Parse(dr["TB_Customer"].ToString());
                if (!dr.IsNull("TB_Agent")) TB_Agent = double.Parse(dr["TB_Agent"].ToString());
                if (!dr.IsNull("SaleName")) SaleName = dr["SaleName"].ToString();
                if (!dr.IsNull("SaleOfProvince")) SaleOfProvince = dr["SaleOfProvince"].ToString();
                if (!dr.IsNull("AccountingName")) AccountingName = dr["AccountingName"].ToString();
                if (!dr.IsNull("AccountingDate")) AccountingDate = DateTime.Parse(dr["AccountingDate"].ToString());
                if (!dr.IsNull("InvoicesNo")) InvoicesNo = dr["InvoicesNo"].ToString();
                if (!dr.IsNull("InvoicesDate")) InvoicesDate = DateTime.Parse(dr["InvoicesDate"].ToString());
                if (!dr.IsNull("InvoicesNoBuy")) InvoicesNoBuy = dr["InvoicesNoBuy"].ToString();
                if (!dr.IsNull("InvoicesDateBuy")) InvoicesDateBuy = DateTime.Parse(dr["InvoicesDateBuy"].ToString());
                if (!dr.IsNull("RateBuy")) RateBuy = double.Parse(dr["RateBuy"].ToString());
                if (!dr.IsNull("RateSell")) RateSell = double.Parse(dr["RateSell"].ToString());
                if (!dr.IsNull("IdProvince")) IdProvince = long.Parse(dr["IdProvince"].ToString());
                if (!dr.IsNull("SaleID")) SaleID = long.Parse(dr["SaleID"].ToString());
                if (!dr.IsNull("AccountingID")) AccountingID = long.Parse(dr["AccountingID"].ToString());
                if (!dr.IsNull("Location")) Location = dr["Location"].ToString();
                if (!dr.IsNull("CloseFile")) CloseFile = int.Parse(dr["CloseFile"].ToString());
                if (!dr.IsNull("InvoicesBuyer")) InvoicesBuyer = dr["InvoicesBuyer"].ToString();
                if (!dr.IsNull("Note")) Note = dr["Note"].ToString();
            }
        }
        public static void GetList(string strCondition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select * from tbl_cost_accounting where 1=1 ";
            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL += " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public static void GetListWithTotal(string strCondition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "SELECT tbl_cost_accounting.*,CONVERT(SUBSTRING_INDEX(tbl_cost_accounting.FileNumber,'-',-1),UNSIGNED INTEGER) AS numFileNumber, " +
                "SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN (tbl_cost_accounting_detail.BuyPriceInvoice* tbl_cost_accounting_detail.Amount) " +
                "WHEN 'USD' THEN (tbl_cost_accounting_detail.BuyPriceInvoice* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateBuy ) END) AS Total_BuyPriceInvoice, " +

                "SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN (tbl_cost_accounting_detail.SellPriceInvoice* tbl_cost_accounting_detail.Amount) " +
                "WHEN 'USD' THEN (tbl_cost_accounting_detail.SellPriceInvoice* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell ) END) AS Total_SellPriceInvoice,  " +

                "SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN ((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice" +
                //COM customer VND
                " + (tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)/Vat_ComCustomer" +
                ")* tbl_cost_accounting_detail.Amount) " +
                "WHEN 'USD' THEN ((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateBuy " +
                //Com customer agent
                " + (tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell/Vat_ComCustomer " +
                ") END) AS Total_Com,  " +

                "SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN (((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice)*tbl_cost_accounting.TB_Agent " +
                //COM customer VND
                " + (tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)*tbl_cost_accounting.TB_Customer " +
                ")* tbl_cost_accounting_detail.Amount/100) " +
                "WHEN 'USD' THEN ((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateBuy*tbl_cost_accounting.TB_Agent/100 " +
                //Com customer agent
                " + (tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell*tbl_cost_accounting.TB_Customer/100 " +
                ") END) AS Total_Tax  " +

                //"SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                //"WHEN 'VND' THEN ((tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)* tbl_cost_accounting_detail.Amount) " +
                //"WHEN 'USD' THEN ((tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell ) END) AS Total_ComCustomer  " +
                //" Total_ComAgent + Total_ComCustomer as Com " +
                "FROM tbl_cost_accounting Left Join tbl_cost_accounting_detail On tbl_cost_accounting.ID= tbl_cost_accounting_detail.CostAccountingID Where 1=1 ";
            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }
            strSQL += " GROUP BY tbl_cost_accounting.ID ";

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL += " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }

        public static void GetSumAllTotal(string strCondition, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "SELECT " +
                "SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN (tbl_cost_accounting_detail.BuyPriceInvoice* tbl_cost_accounting_detail.Amount) " +
                "WHEN 'USD' THEN (tbl_cost_accounting_detail.BuyPriceInvoice* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateBuy ) END) AS Total_BuyPriceInvoice, " +

                "SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN (tbl_cost_accounting_detail.SellPriceInvoice* tbl_cost_accounting_detail.Amount) " +
                "WHEN 'USD' THEN (tbl_cost_accounting_detail.SellPriceInvoice* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell ) END) AS Total_SellPriceInvoice,  " +

                "SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN ((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice" +
                //COM customer VND
                " + (tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)/Vat_ComCustomer" +
                ")* tbl_cost_accounting_detail.Amount) " +
                "WHEN 'USD' THEN ((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateBuy " +
                //Com customer agent
                " + (tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell/Vat_ComCustomer " +
                ") END) AS Total_Com,  " +

                "SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN (((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice)*tbl_cost_accounting.TB_Agent " +
                //COM customer VND
                " + (tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)*tbl_cost_accounting.TB_Customer " +
                ")* tbl_cost_accounting_detail.Amount/100) " +
                "WHEN 'USD' THEN ((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateBuy*tbl_cost_accounting.TB_Agent/100 " +
                //Com customer agent
                " + (tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell*tbl_cost_accounting.TB_Customer/100 " +
                ") END) AS Total_Tax  " +

                //"SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                //"WHEN 'VND' THEN ((tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)* tbl_cost_accounting_detail.Amount) " +
                //"WHEN 'USD' THEN ((tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell ) END) AS Total_ComCustomer  " +
                //" Total_ComAgent + Total_ComCustomer as Com " +
                "FROM tbl_cost_accounting Left Join tbl_cost_accounting_detail On tbl_cost_accounting.ID= tbl_cost_accounting_detail.CostAccountingID Where 1=1 ";
            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }

        public static void GetTotalCostAccountingByID(long strID, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "SELECT tbl_cost_accounting.*,CONVERT(SUBSTRING_INDEX(tbl_cost_accounting.FileNumber,'-',-1),UNSIGNED INTEGER) AS numFileNumber, " +
                "SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN (tbl_cost_accounting_detail.BuyPriceInvoice* tbl_cost_accounting_detail.Amount) " +
                "WHEN 'USD' THEN (tbl_cost_accounting_detail.BuyPriceInvoice* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateBuy ) END) AS TotalBuyInvoice, " +

                "SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN (tbl_cost_accounting_detail.SellPriceInvoice* tbl_cost_accounting_detail.Amount) " +
                "WHEN 'USD' THEN (tbl_cost_accounting_detail.SellPriceInvoice* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell ) END) AS TotalSellInvoice,  " +

                //Com Customer
                "SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN ((tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)* tbl_cost_accounting_detail.Amount/Vat_ComCustomer) " +
                "WHEN 'USD' THEN ((tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell/Vat_ComCustomer ) END) AS TotalComCustomer,  " +
                //Com Agent
                "SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN ((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice)* tbl_cost_accounting_detail.Amount) " +
                "WHEN 'USD' THEN ((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateBuy ) END) AS TotalComAgent,  " +

                //"SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                //"WHEN 'VND' THEN ((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice" +
                ////COM customer VND
                //" + tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice" +
                //")* tbl_cost_accounting_detail.Amount) " +
                //"WHEN 'USD' THEN ((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateBuy " +
                ////Com customer agent
                //" + (tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell " +
                //") END) AS Total_Com,  " +
                //Tax Customer
                "SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN (((tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)/Vat_ComCustomer)* tbl_cost_accounting_detail.Amount*TB_Customer/100) " +
                "WHEN 'USD' THEN (((tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)/Vat_ComCustomer)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell*TB_Customer/100) END) AS TotalTaxCustomer,  " +

                //Tax Agent
                "SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN ((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.TB_Agent/100) " +
                "WHEN 'USD' THEN ((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateBuy*tbl_cost_accounting.TB_Agent/100 ) END) AS TotalTaxAgent  " +
                //"SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                //"WHEN 'VND' THEN (((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice)*tbl_cost_accounting.TB_Agent " +
                ////COM customer VND
                //" + (tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)*tbl_cost_accounting.TB_Customer " +
                //")* tbl_cost_accounting_detail.Amount/100) " +
                //"WHEN 'USD' THEN ((tbl_cost_accounting_detail.BuyPrice - tbl_cost_accounting_detail.BuyPriceInvoice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateBuy*tbl_cost_accounting.TB_Agent/100 " +
                ////Com customer agent
                //" + (tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell*tbl_cost_accounting.TB_Customer/100 " +
                //") END) AS Total_Tax  " +

                //"SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                //"WHEN 'VND' THEN ((tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)* tbl_cost_accounting_detail.Amount) " +
                //"WHEN 'USD' THEN ((tbl_cost_accounting_detail.SellPriceInvoice - tbl_cost_accounting_detail.SellPrice)* tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell ) END) AS Total_ComCustomer  " +
                //" Total_ComAgent + Total_ComCustomer as Com " +
                "FROM tbl_cost_accounting Left Join tbl_cost_accounting_detail On tbl_cost_accounting.ID= tbl_cost_accounting_detail.CostAccountingID Where tbl_cost_accounting.ID= " + strID;
            strSQL += " GROUP BY tbl_cost_accounting.ID ";
            //GV.Response.Write(strSQL);
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
        public static void GetComSaleReport(string strCondition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "SELECT tbl_cost_accounting_detail.Amount,tbl_cost_accounting_detail.ID " +
            ",tbl_cost_accounting_detail.TypeOfCurrency " +
            ",(tbl_cost_accounting_detail.BuyPrice * tbl_cost_accounting_detail.Amount) AS TotalBuyPrice  " +
            ",(tbl_cost_accounting_detail.SellPrice * tbl_cost_accounting_detail.Amount) AS TotalSellPrice  " +
            ",(tbl_cost_accounting_detail.SellPrice - tbl_cost_accounting_detail.BuyPrice)*tbl_cost_accounting_detail.Amount AS GrossProfit  " +
            ",tbl_cost_accounting_detail.Description " +
            ",(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
            "WHEN 'VND' THEN ((tbl_cost_accounting_detail.SellPrice - tbl_cost_accounting_detail.BuyPrice)*tbl_cost_accounting_detail.Amount) " +
            "WHEN 'USD' THEN ((tbl_cost_accounting_detail.SellPrice - tbl_cost_accounting_detail.BuyPrice)*tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell) END) AS ProfitAfterInterest  " +

            ",tbl_cost_accounting.FileNumber " +
            ",CONVERT(SUBSTRING_INDEX(tbl_cost_accounting.FileNumber,'-',-1),UNSIGNED INTEGER) AS numFileNumber " +
            ",tbl_cost_accounting.Customer " +
            ",tbl_cost_accounting.ID  as costingID " +
            ",tbl_cost_accounting.costingID  as costingSaleID " +
            ",tbl_cost_accounting.Agent " +
            ",tbl_cost_accounting.Pol " +
            ",tbl_cost_accounting.Pod " +
            ",tbl_cost_accounting.ETD " +
            ",tbl_cost_accounting.Liabilities " +
            ",tbl_cost_accounting.RateSell " +
            "FROM `tbl_cost_accounting_detail`  " +
            "LEFT JOIN `tbl_cost_accounting` ON tbl_cost_accounting_detail.CostAccountingID=tbl_cost_accounting.ID WHERE 1=1 ";

            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }
            //strSQL += " GROUP BY tbl_cost_accounting.ID ";

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL += " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public static void GetTotalComSaleReport(string strCondition, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "SELECT tbl_cost_accounting_detail.TypeOfCurrency " +
            ",SUM(tbl_cost_accounting_detail.BuyPrice * tbl_cost_accounting_detail.Amount) AS All_TotalBuyPrice  " +
            ",SUM(tbl_cost_accounting_detail.SellPrice * tbl_cost_accounting_detail.Amount) AS All_TotalSellPrice  " +
            ",SUM((tbl_cost_accounting_detail.SellPrice - tbl_cost_accounting_detail.BuyPrice)*tbl_cost_accounting_detail.Amount) AS All_GrossProfit  " +
            ",tbl_cost_accounting.ratesell" +
            ",SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
            "WHEN 'VND' THEN ((tbl_cost_accounting_detail.SellPrice - tbl_cost_accounting_detail.BuyPrice)*tbl_cost_accounting_detail.Amount) " +
            "WHEN 'USD' THEN ((tbl_cost_accounting_detail.SellPrice - tbl_cost_accounting_detail.BuyPrice)*tbl_cost_accounting_detail.Amount*tbl_cost_accounting.RateSell) END) AS ALL_ProfitAfterInterest  " +
            "FROM `tbl_cost_accounting_detail`  " +
            "LEFT JOIN `tbl_cost_accounting` ON tbl_cost_accounting_detail.CostAccountingID=tbl_cost_accounting.ID " +
            "WHERE 1=1 ";
            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }
            strSQL += "GROUP BY tbl_cost_accounting_detail.TypeOfCurrency";

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL += " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public static double GetTotalInterest(string condition, MySqlConnection cn, MySqlTransaction trans)
        {
            double retVal = 0;
            string sqlCommand = "SELECT SUM(CASE tbl_cost_accounting_detail.TypeOfCurrency " +
                "WHEN 'VND' THEN ((((tbl_cost_accounting_detail.BuyPrice * tbl_cost_accounting_detail.Amount) * tbl_cost_accounting.Interest/365)*tbl_cost_accounting.Liabilities)/100) " +
                "WHEN 'USD' THEN ((((tbl_cost_accounting_detail.BuyPrice * tbl_cost_accounting_detail.Amount) * tbl_cost_accounting.Interest/365)*tbl_cost_accounting.Liabilities*tbl_cost_accounting.RateBuy)/100) " +
                "END)  AS TotalInteret " +
                "FROM  tbl_cost_accounting_detail LEFT JOIN `tbl_cost_accounting` ON tbl_cost_accounting_detail.CostAccountingID=tbl_cost_accounting.ID WHERE 1=1 ";
            //"AND tbl_cost_accounting.ID=" + costingID;

            if (condition.Length > 0)
                sqlCommand += condition;
            string tempretVal = DBModule.ExecuteQueryForOneResult(sqlCommand, cn, trans);
            if (tempretVal != null)
                try
                {
                    retVal = double.Parse(tempretVal);
                }
                catch
                {
                    //GV.Response.Write(sqlCommand);
                }
            return retVal;
        }
        public void DoSearch(string strKeywords, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select `ID` from tbl_cost_accounting where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }

        public bool CheckExistFileNumber(string FileNumber, long ID, string ListProvinceID, MySqlConnection cn, MySqlTransaction trans)
        {
            bool retVal = false;
            string strSQL = "";
            DataSet ds = null;
            // build SQL statement
            strSQL = "Select tbl_customerofferprices.CostNumber from tbl_customerofferprices,tbl_adminusers  where tbl_customerofferprices.UserID=tbl_adminusers.ID " +
                    " AND tbl_adminusers.IdProvince in (" + ListProvinceID + ") AND tbl_customerofferprices.CostNumber='" + FileNumber.ToString() + "'";
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count == 0)
                return true;
            strSQL = "Select `ID` from tbl_cost_accounting WHERE FileNumber='" + FileNumber.ToString() + "'";
            if (ID > 0)
                strSQL += " AND ID <>" + ID.ToString();

            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count > 0)
                retVal = true;
            return retVal;
        }
        public void Duplicate(long CostID, string FileNumber, MySqlConnection cn, MySqlTransaction trans)
        {
            //Duplicate CostAccounting
            string strSQL = "INSERT INTO tbl_cost_accounting " +
                    "(CostingID, " +
                    "FileNumber, " +
                    "POL, " +
                    "POD, " +
                    "ETD, " +
                    "ETA, " +
                    "Agent, " +
                    "CarrSale, " +
                    "Customer," +
                    "CustPic, " +
                    "MBLNo, " +
                    "HBLNo, " +
                    "Freight, " +
                    "Liabilities, " +
                    "Interest, " +
                    "TB_Customer," +
                    "TB_Agent, " +
                    "SaleName, " +
                    "SaleOfProvince, " +
                    "AccountingName, " +
                    "AccountingDate, " +
                    "InvoicesNo, " +
                    "InvoicesDate, " +
                    "RateBuy, " +
                    "RateSell," +
                    "IdProvince" +
                    ") " +
                    "Select " +
                    "CostingID, " +
                    "'" + FileNumber + "_Copy', " +
                    "POL, " +
                    "POD, " +
                    "ETD, " +
                    "ETA, " +
                    "Agent, " +
                    "CarrSale, " +
                    "Customer," +
                    "CustPic, " +
                    "MBLNo, " +
                    "HBLNo, " +
                    "Freight, " +
                    "Liabilities, " +
                    "Interest, " +
                    "TB_Customer," +
                    "TB_Agent, " +
                    "SaleName, " +
                    "SaleOfProvince, " +
                    "AccountingName, " +
                    "AccountingDate, " +
                    "InvoicesNo, " +
                    "InvoicesDate, " +
                    "RateBuy, " +
                    "RateSell," +
                    "IdProvince" +
                    " From tbl_cost_accounting Where ID=" + CostID.ToString();
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            //Duplicate CostAccounting Detal
            strSQL = "Select Max(ID) From tbl_cost_accounting";
            string CurrentCostID = DBModule.ExecuteQueryForOneResult(strSQL, cn, trans);
            strSQL = "INSERT INTO tbl_cost_accounting_detail" +
                   "(CostAccountingID, " +
                   "Description, " +
                   "Units, " +
                   "Amount, " +
                   "TypeOfCurrency, " +
                   "BuyPrice, " +
                   "BuyPriceInvoice, " +
                   "SellPrice, " +
                   "SellPriceInvoice," +
                   "Note " +
                   ")" +
                   " Select " +
                   CurrentCostID + ", " +
                   "Description, " +
                   "Units, " +
                   "Amount, " +
                   "TypeOfCurrency, " +
                   "BuyPrice, " +
                   "BuyPriceInvoice, " +
                   "SellPrice, " +
                   "SellPriceInvoice," +
                   "Note FROM tbl_cost_accounting_detail Where CostAccountingID=" + CostID.ToString();
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }
    }
}


